package fr.mywiki.model.queries;

import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import fr.mywiki.business.entry.EntryLite;
import fr.mywiki.business.entry.meta.Property;

/** Several methods to directly manipulate the entry objects in the database */
public class DatabaseEntryController {
  /**
   * Returns the last lock time concerning this entry.
   * 
   * @param entry
   *          a <code>EntryLite</code> object.
   * @return a <code>Date</code> object, the last lock on this entry.
   * TODO check the date conversion
   * TODO check with an unlocked entry
   */
  public static Date getEntryLock(EntryLite entry) throws SQLException,
      ParseException {
    String sql = "SELECT " + EntryLite.FIELD_LOCKTIME + " FROM "
        + EntryLite.TYPE_ENT + " WHERE id=" + entry.getId().toString();
    String date = (String) DatabaseAdapter.executeSelectQuery(sql).getRecord(0)[0];
    if (date != null) {
      DateFormat df = DateFormat.getDateTimeInstance();
      return df.parse(date);
    }
    return null;
  }
  
  /**
   * Puts a lock on the entry given in parameter.
   * 
   * @param entry a <code>EntryLite</code> object
   */
  public static void putLockOnEntry(EntryLite entry) throws SQLException {
    String sql = "UPDATE " + EntryLite.TYPE_ENT + " SET "
        + EntryLite.FIELD_LOCKTIME + "=? WHERE id=" + entry.getId().toString();
    List arguments = new ArrayList();
    arguments.add(new Date());
    
    DatabaseAdapter.executeUpdateQuery(sql,arguments);
  }
  
  /**
   * Puts the entry given in parameter in an unlocked state.
   * 
   * @param entry a <code>EntryLite</code> object
   */
  public static void removeLockOnEntry(EntryLite entry) throws SQLException {
    String sql = "UPDATE " + EntryLite.TYPE_ENT + " SET "
        + EntryLite.FIELD_LOCKTIME + "=NULL WHERE id=" + entry.getId().toString();
    
    DatabaseAdapter.executeUpdateQuery(sql);
  }
  
  /*
	 * Entry property manipulation. Each record of the table must contain references
	 * to the entry and the model property. The last column of the table is the
	 * content of the property.
	 */
  
  /** The table in which the property contents are stored. */
	private static final String PROP_CONTENT_TABLE = "propcontent";
	
	/** The name of the column containing the entry id */
	private static final String COL_ENTRY_ID = "entry_id";
	
	/** The name of the column containing the model property name */
	private static final String COL_Property_NAME = "name";
	
	/** The name of the column containing the version id */
	private static final String COL_VERSION_ID = "version_id";
	
	/** The name of the column containing the property value. */
	private static final String COL_PROP_CONTENT = "value";
	
	/**
	 * Computes and returns the content of the property for the given entry.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object
	 * @param property
	 *          a <code>Property</code> object
	 * @return a String object describing the content of the property.
	 */
	public static String getContent(EntryLite entry, Property property) throws SQLException {
		String sql = "SELECT " + COL_PROP_CONTENT + " FROM " + PROP_CONTENT_TABLE
				+ " WHERE " + COL_ENTRY_ID + "=" + entry.getId() + " AND "
				+ COL_Property_NAME + "='" + property.getName()+"';";
		ResultList result = DatabaseAdapter.executeSelectQuery(sql);
		if(result.isEmpty())
			return null;
		return ((Object[])result.iterator().next())[0].toString();
	}
	
	/**
	 * Sets the content of the specified proeprty for the given entry to the specified value
	 * @param entry a <code>EntryLite</code> object
	 * @param property a <code>Property</code> object
	 * @param content a <code>String</code> object, the content of the property.
	 */
	public static void setContent(EntryLite entry, Property property, String value) throws SQLException {
		if (!existsContentProperty(entry, property))
			createContentProperty(entry, property);
		modifyContent(entry, property, value);
	}
	
	/**
	 * Modifies the content of an entry's property.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 * @param property
	 *          a <code>Property</code> object.
	 * @param content
	 *          a <code>String</code> object, the content of the property.
	 */
	private static void modifyContent(EntryLite entry, Property property,
			String content) throws SQLException {
		String sql = "UPDATE " + PROP_CONTENT_TABLE + " SET " + COL_PROP_CONTENT
				+ "='" + content + "' WHERE " + COL_ENTRY_ID + "=" + entry.getId()
				+ " AND " + COL_Property_NAME + "='" + property.getName() + "';";
		DatabaseAdapter.executeUpdateQuery(sql);
	}
	
	/**
	 * Creates the empty properties destined to get the contents.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 * @param property
	 *          a <code>Property</code> property.
	 */
	private static void createContentProperty(EntryLite entry, Property property) throws SQLException {
		String sql = "INSERT INTO " + PROP_CONTENT_TABLE + " VALUES ("
				+ entry.getId() + ", " + property.getName() + ", NULL);";
		DatabaseAdapter.executeUpdateQuery(sql);
	}
	
	/**
	 * Indicates if a content record for the specified entry and property
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 * @param property
	 *          a <code>Property</code> property.
	 * @return true if there is a record relating to entry and property.
	 */
	private static boolean existsContentProperty(EntryLite entry, Property property) throws SQLException {
		String sql = "SELECT * FROM " + PROP_CONTENT_TABLE + " WHERE"
				+ COL_ENTRY_ID + "=" + entry.getId() + " AND " + COL_Property_NAME + "="
				+ property.getName() + ";";
		return !DatabaseAdapter.executeSelectQuery(sql).isEmpty();
	}
	
	/**
	 * Deletes the specified property content record.
	 * 
	 * @param entry
	 *          a <code>EntryLite</code> object.
	 * @param property
	 *          a <code>Property</code> property.
	 */
	public static void deleteContentProperty(EntryLite entry, Property property) throws SQLException {
		String sql = "DELETE FROM " + PROP_CONTENT_TABLE + " WHERE "
				+ COL_ENTRY_ID + "=" + entry.getId() + " AND " + COL_Property_NAME + "="
				+ property.getName() + ";";
		DatabaseAdapter.executeUpdateQuery(sql);
	}
}